/********************************************/
// Creation History
// --------------------------------------------------------------
//	Company:			HTHK
//	NAME:				ProjectGroupReportSearch.java
//    Title:		        
//	FUNCTION:			
//	BY:					Teng Yang
//	DATE:				21-10-2013
// --------------------------------------------------------------
/*********************************************/
// Copyright (c) 2013 HTHK
package tss.projectgroupreport;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;

import tss.common.DBServlet2;

import com.ffpc.sql.DataException;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

/**
 * A Class class.
 * <P>
 * 
 * @author HTHK
 */
public class ProjectGroupReportSearch extends DBServlet2 {
	private static final long serialVersionUID = -1742467122571643722L;
	private Logger logger = Logger.getLogger(ProjectGroupReportSearch.class);
	@Override
	protected String perform(HttpServletRequest request, HttpServletResponse response, StrutsMessage message) throws ServletException,
			IOException, SQLException, DataException, Exception {
		String month = request.getParameter("month");
		String[] selectedProjects = request.getParameterValues("selectedProjects");
		mergeSelectedProjects(selectedProjects);
		return downloadResult(response,month,selectedProjects);
	}

	private void mergeSelectedProjects(String[] selectedProjects){
		Connection conn = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			String deleteSql = "delete from tss_c_lookup t where t.field_name = 'SELECTED_PROJECTS'";
			String insertSql = "insert into tss_c_lookup values(?,?,?,?,?,?,?,?,?)";
			String idSql = "select SEQ_TSS_C_LOOKUP_ID.nextval as lookupid from dual";
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.executeUpdate(deleteSql);
			
			pstmt = conn.prepareStatement(insertSql);
			int priority = 1;
			for(String group : selectedProjects){
				rs = stmt.executeQuery(idSql);
				while(rs.next()){
					pstmt.setInt(1, rs.getInt("lookupid"));
				}
				pstmt.setString(2, "varchar2");
				pstmt.setString(3, "SELECTED_PROJECTS");
				pstmt.setString(4, group);
				pstmt.setInt(5, priority++);
				pstmt.setDate(6, new Date(System.currentTimeMillis()));
				pstmt.setString(7, getUser().getUser_id());
				pstmt.setDate(8, null);
				pstmt.setString(9, null);
				pstmt.executeUpdate();
			}
			conn.commit();
		}catch(Exception e){
			logger.error("",e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logger.error("",e1);
			}
		}finally{
			JdbcUtil.release(pstmt, rs);
			JdbcUtil.release(stmt, rs);
		}
	}
	
	private List<Map<String,String>> getRecords(String month, String[] selectedProjects) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String,String>> records = new ArrayList<Map<String,String>>();
		try{
			StringBuffer groupSqlBuffer = new StringBuffer();
			for(String group : selectedProjects){
				groupSqlBuffer.append("'" + group + "',");
			}
			String groupSql = groupSqlBuffer.toString().substring(0, groupSqlBuffer.toString().length() - 1);
			String sql = "SELECT *" +
					 "FROM (SELECT B.FIRST_NAME || ' ' || B.SURNAME STAFF," +
					              "B.POSITION," +
					              "C.PROJECT_GROUP_NAME PROJ," +
					              "SPEND " +
					         "FROM TSS_V_PROJECT_REPORT A, TSS_C_EMPLOYEE B, TSS_C_PROJECT C " +
					        "WHERE A.STAFF_NO = B.STAFF_NO " +
					          "AND TO_CHAR(A.SPEND_DATE, 'yyyymm') = ? " +
					          "AND A.PROJECT_NAME = C.PROJECT_NAME) PIVOT(SUM(SPEND) FOR PROJ IN (" + groupSql + ")) " +
					"ORDER BY STAFF";
			logger.info("project group SQL:" + sql);
			
			conn = this.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, month);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,String> resultMap = new HashMap<String,String>();
				resultMap.put("staff", rs.getString("STAFF"));
				resultMap.put("position", rs.getString("POSITION"));
				for(String group : selectedProjects){
					resultMap.put(group, rs.getString("'" + group + "'")); 
				}
				records.add(resultMap);
			}
		}catch(Exception e){
			logger.error("", e);
		}finally{
			JdbcUtil.release(pstmt, rs);
		}
		return records;
	}
	
	private String downloadResult(HttpServletResponse response,String month,String[] selectedProjects) {
		response.reset();
		String resultFile = null;
		resultFile = "ProjGrp_" + month + ".xls";
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename=" + resultFile);
		OutputStream output = null;
		try {
			output = response.getOutputStream();
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Group Report");
			HSSFRow row_title = sheet.createRow(0);
			
			HSSFCellStyle style = workbook.createCellStyle();
			style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
			style.setFillPattern(CellStyle.SOLID_FOREGROUND);
			style.setBorderTop(CellStyle.BORDER_THIN);
			style.setTopBorderColor(HSSFColor.BLACK.index);
			style.setBorderBottom(CellStyle.BORDER_THIN);
			style.setBottomBorderColor(HSSFColor.BLACK.index);
			style.setBorderLeft(CellStyle.BORDER_THIN);
			style.setLeftBorderColor(HSSFColor.BLACK.index);
			style.setBorderRight(CellStyle.BORDER_THIN);
			style.setRightBorderColor(HSSFColor.BLACK.index);
			
			HSSFCell row_title_cell_staff = row_title.createCell(0);
			row_title_cell_staff.setCellValue(new HSSFRichTextString("STAFF"));
			row_title_cell_staff.setCellStyle(style);
			
			HSSFCell row_title_cell_position = row_title.createCell(1);
			row_title_cell_position.setCellValue(new HSSFRichTextString("POSITION"));
			row_title_cell_position.setCellStyle(style);
			
			int titleCellNo = 2;
			for(String group : selectedProjects){
				HSSFCell row_title_cell_group = row_title.createCell(titleCellNo);
				row_title_cell_group.setCellValue(new HSSFRichTextString(group.toUpperCase()));
				row_title_cell_group.setCellStyle(style);
				titleCellNo++;
			}
			
			
			
			List<Map<String,String>> records = getRecords(month,selectedProjects);
			for (int i = 0; i < records.size(); i++) {
				Map<String,String> resultMap = records.get(i);
				HSSFRow row = sheet.createRow(i + 1);
				
				HSSFCell row_cell_staff = row.createCell(0);
				row_cell_staff.setCellValue(new HSSFRichTextString(resultMap.get("staff")));
				
				HSSFCell row_cell_position = row.createCell(1);
				row_cell_position.setCellValue(new HSSFRichTextString(resultMap.get("position")));
				
				int cellNo = 2;
				for(String group : selectedProjects){
					HSSFCell row_cell_group = row.createCell(cellNo);
					String groupValue = resultMap.get(group);
					row_cell_group.setCellType(Cell.CELL_TYPE_NUMERIC);
					if(groupValue == null || "".equals(groupValue.trim())){
						row_cell_group.setCellValue(groupValue);
					}else{
						row_cell_group.setCellValue(Float.parseFloat(groupValue));
					}
					
					cellNo++;
				}
			}
			workbook.write(output);
			output.flush();
		} catch (Exception ex) {
			logger.error("Export fails", ex);
		} finally {
			try {
				if (output != null) {
					output.close();
					output = null;
				}
			} catch (Exception e) {
				logger.error("", e);
			}
		}
		return null;
	}
}
